import { withRootAuth } from '../../utils/auth'
import type { ApiResponse } from '../../../types'

/**
 * 订单查询接口响应数据结构
 */
interface OrderData {
  id: number
  order_sn: string
  title: string
  type: string
  email: string
  goods_name: string
  username: string
  total_price: number
  actual_price: number
  status: string
  status_code: number
  created_at: string
  updated_at: string
}

/**
 * 分页信息结构
 */
interface PaginationData {
  page: number
  limit: number
  total: number
  pages: number
}

/**
 * 订单查询接口处理函数
 * 支持按订单编号、订单名称、邮箱进行查询
 * 需要root权限
 */
async function handleOrdersQuery(event: any): Promise<ApiResponse<{ orders: OrderData[], pagination: PaginationData }>> {
  try {
    // 权限验证已在withRootAuth中完成，可以直接访问用户信息
    const user = event.context.user

    // 获取数据库连接
    const db = event.context.cloudflare.env.DB as D1Database
    if (!db) {
      console.error('D1数据库绑定未在事件上下文中找到')
      return {
        success: false,
        message: '服务器配置错误，数据库不可用'
      }
    }

    // 获取查询参数
    const query = getQuery(event)
    const {
      order_sn = '', // 订单编号
      title = '', // 订单名称
      email = '', // 邮箱
      status = '', // 状态
      page = 1,
      limit = 20
    } = query
    
    // 构建查询条件
    let whereConditions = ['o.deleted_at IS NULL']
    let params: any[] = []
    
    if (order_sn) {
      whereConditions.push('o.order_sn LIKE ?')
      params.push(`%${order_sn}%`)
    }
    
    if (title) {
      whereConditions.push('o.title LIKE ?')
      params.push(`%${title}%`)
    }
    
    if (email) {
      whereConditions.push('o.email LIKE ?')
      params.push(`%${email}%`)
    }
    
    if (status) {
      whereConditions.push('o.status = ?')
      params.push(Number(status))
    }
    
    const whereClause = whereConditions.join(' AND ')
    
    // 查询总数
    const countSql = `
      SELECT COUNT(*) as total 
      FROM orders o
      WHERE ${whereClause}
    `
    const countResult = await db.prepare(countSql).bind(...params).first()
    const total = Number(countResult?.total) || 0
    
    // 分页查询订单数据
    const offset = (Number(page) - 1) * Number(limit)
    const sql = `
      SELECT 
        o.id,
        o.order_sn,
        o.title,
        o.type,
        o.email,
        o.total_price,
        o.actual_price,
        o.status,
        o.created_at,
        o.updated_at,
        g.gd_name as goods_name,
        u.username
      FROM orders o
      LEFT JOIN goods g ON o.goods_id = g.id
      LEFT JOIN users u ON o.user_id = u.id
      WHERE ${whereClause}
      ORDER BY o.created_at DESC
      LIMIT ? OFFSET ?
    `
    
    const result = await db.prepare(sql).bind(...params, Number(limit), offset).all()
    
    // 格式化订单数据
    const formattedOrders: OrderData[] = (result.results || []).map((order: any) => ({
      id: Number(order.id) || 0,
      order_sn: String(order.order_sn || ''),
      title: String(order.title || ''),
      type: Number(order.type) === 1 ? '自动发货' : '人工处理',
      email: String(order.email || ''),
      goods_name: String(order.goods_name || '未知商品'),
      username: String(order.username || '匿名用户'),
      total_price: Number(order.total_price) || 0,
      actual_price: Number(order.actual_price) || 0,
      status: getOrderStatusText(Number(order.status)),
      status_code: Number(order.status) || 0,
      created_at: String(order.created_at || ''),
      updated_at: String(order.updated_at || '')
    }))
    
    return {
      success: true,
      message: '查询成功',
      data: {
        orders: formattedOrders,
        pagination: {
          page: Number(page),
          limit: Number(limit),
          total,
          pages: Math.ceil(total / Number(limit))
        }
      }
    }
    
  } catch (error) {
    console.error('订单查询失败:', error)
    
    return {
      success: false,
      message: '获取订单数据失败，请稍后重试'
    }
  }
}

/**
 * 订单查询接口
 * 使用withRootAuth装饰器自动进行root权限验证
 */
export default defineEventHandler(withRootAuth(handleOrdersQuery))

/**
 * 获取订单状态文本
 * @param {number} status - 状态码
 * @returns {string} 状态文本
 */
function getOrderStatusText(status: number) {
  const statusMap = {
    1: '待支付',
    2: '待处理', 
    3: '处理中',
    4: '已完成',
    5: '处理失败',
    6: '异常',
    '-1': '已过期'
  }
  
  return statusMap[status as keyof typeof statusMap] || '未知状态'
}